package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import model.Customer;
import model.OrderItem;
import model.Orders;
import model.Product;
import oracle.jdbc.pool.OracleDataSource;
import test.Main;

public class OrderOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	public OrderOperations()
	{
		conn=openDB();
	}
	
	// open database connection
	public Connection openDB()
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}

			conn = ods.getConnection();
			System.out.println("connected order.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	
	// drop customer table from database
	public void dropOrderTable() 
	{
		System.out.println("Checking for existing tables.");
		try
		{
			stm = conn.createStatement();

			try 
			{
				// Dropping a sequence
				String dropseq = "drop sequence ord_seq";
				stm = conn.prepareStatement(dropseq);
				stm.executeUpdate(dropseq);
			}
			catch(SQLSyntaxErrorException e)
				{
					
				}
				stm.execute("DROP TABLE Orders");
				System.out.println("Order table dropped.");
			
		} 
		catch (SQLException ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
			//ex.printStackTrace();
		}
	}
	
	// create a order table in database
		public void CreateOrderTable() 
		{
			try 
			{
				stm = conn.createStatement();
				String create = "CREATE TABLE Orders "
						+ "(ord_num NUMBER PRIMARY KEY, cust_id NUMBER, user_id NUMBER, FOREIGN KEY (cust_id) REFERENCES Customer, FOREIGN KEY (user_id) REFERENCES Users)";

				stm.executeUpdate(create);
				System.out.println("order table created");
				
				// Creating a sequence
				String createseq = "create sequence ord_seq increment by 1 start with 1 nocache";
				stm = conn.createStatement();
				stm.executeUpdate(createseq);
			} 
			catch (SQLException e) 
			{
				System.out.print("SQL Exception " + e);
				System.exit(1);
			}
		}
		
		// delete a order from table
		public void deleteOrder(int o) 
		{
			String cmd=null;
			try 
			{
				cmd = "delete from orderitem where ord_num="+o;
				stm = conn.createStatement();
				stm.executeUpdate(cmd);
				System.out.println("oi");
			}
			catch (Exception e) 
			{
				System.out.println(e);
			}
			try
			{
				cmd = "delete from day where ord_num="+o;
				stm = conn.createStatement();
				stm.executeUpdate(cmd);
				System.out.println("d");
			}
			catch (Exception e) 
			{
				System.out.println(e);
			}
			try
			{
				cmd = "delete from orders where ord_num="+o;
				stm = conn.createStatement();
				stm.executeUpdate(cmd);
				System.out.println("o");
			}
			catch (Exception e) 
			{
				System.out.println(e);
			}
			try
			{
				cmd = "delete from customer where cust_id="+o;
				stm = conn.createStatement();
				stm.executeUpdate(cmd);
				System.out.println("c");
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
		}
		
		// add a order to the table
		public void addOrder(Orders e) 
		{
			try 
			{
				String sql = "INSERT INTO Orders(ord_num,cust_id,user_id) VALUES (ord_seq.nextVal,?,?)";

				prst = conn.prepareStatement(sql);
				prst.setInt(1, e.getCustId());
				prst.setInt(2, e.getUserId());

				prst.executeUpdate();
			} 
			catch (Exception se) 
			{
				System.out.println(se);
			}
		}
		// return a result set of all customers in table
		public ResultSet getCustomer() 
		{
			try 
			{
				String queryString = "SELECT * FROM Customer ORDER BY cust_id";

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
			return rst;
		}
		
		//get current order number
		public int getCurrentVal()
		{
			int a=0;
			try
			{
				String queryString = "select ord_seq.currval from user_sequences";
				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
							
				while(rst.next())
					{
						a=rst.getInt(1);
					}
			}
			catch(SQLException e)
			{
				
			}
			return a;
		}
		
		//get order number by customer number
		public int findOrdNum(int c)
		{
			int a=0;
			try
			{
				String queryString = "select * from orders where cust_id="+c;
				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
				while(rst.next())
					{
						if(rst.getInt(3)==Main.userId || Main.userId==1)
						{
							a=rst.getInt(1);
						}
						else
						{
							JOptionPane.showMessageDialog(null,"You are not authorized to view this information","ERROR",JOptionPane.WARNING_MESSAGE);
						}
					}
			}
			catch(SQLException e)
			{
				
			}
			return a;
		}
		
		//get total order price
		public double totalOrderPrice(int o)
		{
			double a=0;
			try
			{
				String queryString = "select o.user_id, sum(oi.prod_qty*p.prod_price) "+
										"from orders o,day d,orderitem oi,product p "+
										"where oi.prod_id=p.prod_id "+
										"and oi.ord_num=d.ord_num "+
										"and oi.day_id=d.day_id "+
										"and d.ord_num=o.ord_num "+
										"and oi.ord_num="+o+
										" group by o.user_id";
				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
				while(rst.next())
					{
						if(rst.getInt(1)==Main.userId || Main.userId==1)
						{
							a=rst.getDouble(2);
						}
						else
						{
							//JOptionPane.showMessageDialog(null,"You are not authorized to view this information","ERROR",JOptionPane.WARNING_MESSAGE);
						}
					}
			}
			catch(SQLException e)
			{
				
			}
			return a;
		}
		
		//get daily order price
		public double dailyOrderPrice(int o,int d)
		{
			double a=0;
			try
			{
				String queryString = "select o.user_id, sum(oi.prod_qty*p.prod_price) "+
										"from orders o,day d,orderitem oi,product p "+
										"where oi.prod_id=p.prod_id "+
										"and oi.ord_num=d.ord_num "+
										"and oi.day_id=d.day_id "+
										"and d.ord_num=o.ord_num "+
										"and oi.ord_num="+o+
										" and oi.day_id="+d+
										" group by o.user_id";
				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
				while(rst.next())
					{
						if(rst.getInt(1)==Main.userId || Main.userId==1)
						{
							a=rst.getDouble(2);
						}
						else
						{
							//JOptionPane.showMessageDialog(null,"You are not authorized to view this information","ERROR",JOptionPane.WARNING_MESSAGE);
						}
					}
			}
			catch(SQLException e)
			{
				
			}
			return a;
		}
		
		//get order info
		public ArrayList<OrderItem> getOrderInfo(int o,int d)
		{
			ArrayList<OrderItem> list=new ArrayList<OrderItem>();
			OrderItem oi;
			try
			{
				String queryString = "select * from orderitem where ord_num="+o+" and day_id="+d;
				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
				while(rst.next())
					{
						oi=new OrderItem(rst.getInt(1),rst.getInt(2),rst.getInt(3),rst.getInt(4));
						list.add(oi);
					}
			}
			catch(SQLException e)
			{
				
			}
			return list;
		}
		
		//get prod name from its id
		public ArrayList<Product> getProdId(int n)
		{
			ArrayList<Product> list=new ArrayList<Product>();
			try 
			{
				String queryString = "SELECT * FROM Product where prod_id="+n;

				stm = conn.createStatement();
				ResultSet rst2 = stm.executeQuery(queryString);
				while(rst2.next())
				{
					Product p=new Product(rst2.getInt(1),rst2.getString(2),rst2.getString(3),rst2.getString(4),rst2.getDouble(5));
					list.add(p);
				}
			} 
			catch (SQLException e) 
			{
				System.out.println(e);
			}
			return list;
		}
		
		//check if day is in day table
		public boolean checkDay(int o,int d)
		{
			boolean well=false;
			try 
			{
				String queryString = "SELECT * from day where ord_num="+o+" and day_id="+d;

				stm = conn.createStatement();
				ResultSet rst2 = stm.executeQuery(queryString);
				while(rst2.next())
				{
					well=true;
				}
			} 
			catch (SQLException e) 
			{
				System.out.println(e);
			}
			return well;
		}
		
		//check if product already in day of order
		public boolean checkProdOnDay(int o,int d,int p)
		{
			boolean well=false;
			try 
			{
				String queryString = "SELECT * from orderitem where ord_num="+o+" and day_id="+d+" and prod_id="+p;

				stm = conn.createStatement();
				ResultSet rst2 = stm.executeQuery(queryString);
				while(rst2.next())
				{
					well=true;
				}
			} 
			catch (SQLException e) 
			{
				System.out.println(e);
			}
			return well;
		}
	
		
		//add day to day table
		public void addOrderDay(int o,int d)
		{
			try 
			{
				String sql = "INSERT INTO Day(ord_num,day_id,active) VALUES (?,?,?)";

				prst = conn.prepareStatement(sql);
				prst.setInt(1, o);
				prst.setInt(2, d);
				prst.setString(3, "Y");
				prst.executeUpdate();
				System.out.println("day added");
			} 
			catch (SQLException e) 
			{
				System.out.println(e);
			}
		}
		
		//change quantity of product order
		public void changeQty(int o,int d,int p,int q)
		{
			try 
			{
				String sql = "UPDATE orderitem SET prod_qty=" + q + " where ord_num="+o+" and day_id="+d+" and prod_id="+p;
				stm = conn.createStatement();
				stm.executeUpdate(sql);	
			} 
			catch (SQLException e) 
			{
				System.out.println(e);
			}
		}
		
		//add a product to an order
		public void addProduct(int o,int d,int p,int q)
		{
			try 
			{
				String sql = "INSERT INTO ORDERITEM(ord_num,day_id,prod_id,prod_qty) VALUES (?,?,?,?)";

				prst = conn.prepareStatement(sql);
				prst.setInt(1, o);
				prst.setInt(2, d);
				prst.setInt(3, p);
				prst.setInt(4, q);
				

				prst.executeUpdate();
			} 
			catch (Exception se) 
			{
				System.out.println(se);
			}
		}
		
		
		//remove a product from an order
		public void removeOrder(int o,int d,int p)
		{
			try 
			{
				String queryString = "delete from orderitem where ord_num="+o+" and day_id="+d+" and prod_id="+p;

				stm = conn.createStatement();
				stm.executeQuery(queryString);
			} 
			catch (SQLException e) 
			{
				System.out.println(e);
			}
		}
		
		// close the connection to database
		public void closeDB() 
		{
			try 
			{
				stm.close();
				rst.close();
				conn.close();
				System.out.print("Connection closed order");
			} 
			catch (SQLException e) 
			{
				System.out.print("Could not close connection ");
				e.printStackTrace();
			}
		}
		
		public void closeDB2() 
		{
			try 
			{
				stm.close();
				//rst.close();
				conn.close();
				System.out.print("Connection closed order");
			} 
			catch (SQLException e) 
			{
				System.out.print("Could not close connection ");
				e.printStackTrace();
			}
		}
}
